package cs4280.dao;

import cs4280.bean.Member;
import cs4280.jdbc.DB;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class MemberDAO {

    public static enum RESULT {

        OK, DUPLICATE, FAIL
    };

    public ArrayList<Member> getMembers() {
        ArrayList<Member> members = new ArrayList<Member>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT [id], [username], [password], [lp], [role] FROM [member];");
            while (rs != null && rs.next() != false) {
                Member member = new Member();
                member.setId(Integer.parseInt(rs.getString(1)));
                member.setUsername(rs.getString(2));
                member.setPassword(rs.getString(3));
                member.setLp(rs.getInt(4));
                member.setRole(rs.getString(5));
                members.add(member);
            }
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return members;
    }

     public ArrayList<Member> getOfficers() {
        ArrayList<Member> members = new ArrayList<Member>();

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            //Statement stmt = conn.createStatement();

            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [username], [password], [lp], [role] FROM [member] WHERE [role]=?;");
            pstmt.setString(1, "Officer");
            ResultSet rs = pstmt.executeQuery();
            while (rs != null && rs.next() != false) {
                Member member = new Member();
                member.setId(Integer.parseInt(rs.getString(1)));
                member.setUsername(rs.getString(2));
                member.setPassword(rs.getString(3));
                member.setLp(rs.getInt(4));
                member.setRole(rs.getString(5));
                members.add(member);
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return members;
    }

    public Member getMember(String username) {
        Member member = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [username], [password], [lp], [role] FROM [member] WHERE [username] = ?;");
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                member = new Member();
                member.setId(rs.getInt(1));
                member.setUsername(rs.getString(2));
                member.setPassword(rs.getString(3));
                member.setLp(rs.getInt(4));
                member.setRole(rs.getString(5));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return member;
    }

    public Member getMember(int userID) {
        Member member = null;

        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT [id], [username], [password], [lp], [role] FROM [member] WHERE [id] = ?;");
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            if (rs != null && rs.next() != false) {
                member = new Member();
                member.setId(rs.getInt(1));
                member.setUsername(rs.getString(2));
                member.setPassword(rs.getString(3));
                member.setLp(rs.getInt(4));
                member.setRole(rs.getString(5));
            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (javax.naming.NamingException ne) {
            /* ignore */
        } catch (SQLException sqle) {
            /* ignore */
        }

        return member;
    }

        public RESULT updateMember(Member newMember) {

        RESULT result = MemberDAO.RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [member] WHERE [username] =?");
            pstmt.setString(1, newMember.getUsername());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            //member exists

            if (rs != null && rs.next() != false) {
                pstmt = conn.prepareStatement("UPDATE [member] SET [password]=?,[role]=?,[lp]=? WHERE [username]=?");
                
                pstmt.setString(1, newMember.getPassword());
                pstmt.setString(2, newMember.getRole());
                pstmt.setInt(3, newMember.getLp());
                pstmt.setString(4,newMember.getUsername());
                

                int rows = pstmt.executeUpdate();

                //successfully add the movie
                if (rows > 0) {
                    result = MemberDAO.RESULT.OK;
                } //insert movie fail
            } // return duplicate msg

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }

        public RESULT deleteMember(Member d_member) {

        RESULT result = RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [member] WHERE [username] =?");
            pstmt.setString(1, d_member.getUsername());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            //movie exists

            if (rs != null && rs.next() != false) {
                pstmt = conn.prepareStatement("DELETE FROM [member] WHERE [username]=?");
                pstmt.setString(1, d_member.getUsername());

                int rows = pstmt.executeUpdate();

                PreparedStatement s_pstmt = conn.prepareStatement("DELETE FROM [screening] WHERE [movieid]=?");
                s_pstmt.setInt(1, d_member.getId());

                int cols = pstmt.executeUpdate();


                //successfully delete the movie
                if (rows > 0) {

                    result = MemberDAO.RESULT.OK;
                } //insert movie fail


            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }


    public RESULT addMember(Member newMember) {

        RESULT result = MemberDAO.RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [member] WHERE [username] =?");
            pstmt.setString(1, newMember.getUsername());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            //there is no duplicate movie

            if (rs != null && rs.next() != false) {
                result = MemberDAO.RESULT.DUPLICATE;
            } // return duplicate msg
            else {


                pstmt = conn.prepareStatement("INSERT INTO [member]([username],[password],[lp],[role]) VALUES(?,?,?,?)");
                pstmt.setString(1, newMember.getUsername());
                pstmt.setString(2, newMember.getPassword());
                pstmt.setInt(3, newMember.getLp());
                pstmt.setString(4, newMember.getRole());

                int rows = pstmt.executeUpdate();

                //successfully add the movie
                if (rows > 0) {
                    result = MemberDAO.RESULT.OK;
                } //insert movie fail
                else {
                    result = MemberDAO.RESULT.FAIL;
                }

            }
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;

    }

    public RESULT changeLoyaltyPoint(int memberid, int cLP) {
        RESULT msg = MemberDAO.RESULT.FAIL;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("UPDATE [member] SET lp=? WHERE id= ?");
            pstmt.setInt(1, cLP);
            pstmt.setInt(2, memberid);

            int rows = pstmt.executeUpdate();

            //successfully add the movie
            if (rows > 0) {
                msg = MemberDAO.RESULT.OK;
            } //insert movie fail
            else {
                msg = MemberDAO.RESULT.FAIL;
            }

            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return msg;

    }

    public boolean validateMember(Member member) {
        boolean result = false;
        try {
            DataSource ds = DB.getDataSource();
            Connection conn = ds.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM [member] WHERE [username] =?");
            pstmt.setString(1, member.getUsername());
            //boolean exist = pstmt.execute();
            ResultSet rs = pstmt.executeQuery();

            //valide member

            if (rs != null && rs.next() != false) {

                if (rs.getString("password").equals(member.getPassword())) {
                    result = true;
                }

            }

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (NamingException ne) {

            /*ignore*/
        } catch (SQLException sqle) {

            /*ignore*/
        }
        return result;


    }
}
